package me.lzl.myblog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import me.lzl.myblog.domain.Article;
import me.lzl.myblog.domain.Page;
import me.lzl.myblog.utils.DBUtils;

/**
 * 文章数据访问对象
 * 
 * @author 梁子力
 *
 */
public class ArticleDAO {

	public List<Article> findAll() throws SQLException {

//		1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 order by a.id desc";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		List<Article> articleList = new ArrayList<>();
		while (rs.next()) {
//			id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));
			articleList.add(article);
		}

//		5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return articleList;
	}

	private int countAll(String sql) throws SQLException {
		Connection conn = DBUtils.getConnection();

		PreparedStatement st = conn.prepareStatement(sql);
		ResultSet rs = st.executeQuery();
		int totalCount = 0;
		while (rs.next()) {
			totalCount = rs.getInt(1);
		}
		DBUtils.closeResource(conn, st, rs);
		return totalCount;

	}

	public Page<Article> findAll(int pageNo) throws SQLException {

		String countSql = "select count(1) from article a inner join category b on a.category_id = b.id where a.status = 1";
		int totalCount = this.countAll(countSql);
		Page<Article> page = new Page<>(pageNo);
		page.setTotalCount(totalCount);

		Connection conn = DBUtils.getConnection();

		String sql = "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 order by id desc limit ?, ?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, page.getStart());
		st.setInt(2, page.getPageSize());
		ResultSet rs = st.executeQuery();

		List<Article> articeList = new ArrayList<>();
		while (rs.next()) {
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setView(rs.getInt("view"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setContent(rs.getString("content"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setTitle(rs.getString("title"));
			article.setDescription(rs.getString("description"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setStatus(rs.getInt("status"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setBanner(rs.getBoolean("banner"));
			articeList.add(article);
		}
		DBUtils.closeResource(conn, st, rs);

		page.setData(articeList);

		return page;
	}

	public List<Article> findAll(int pageNumber, int pageSize) throws SQLException {

//		1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*, b.title as category_title from article a inner join category b on a.category_id=b.id order by id desc limit ?,?";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, (pageNumber - 1) * pageSize);
		st.setInt(2, pageSize);

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		List<Article> articleList = new ArrayList<>();
		while (rs.next()) {
//			id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));
			articleList.add(article);
		}

//		5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return articleList;
	}

	/**
	 * 推荐文章集合
	 * 
	 * @param limit
	 * @return
	 * @throws SQLException
	 */
	public List<Article> findFeaturedFirst(int limit) throws SQLException {

//		1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*, b.title as category_title from article a inner join category b on a.category_id = b.id where a.status = 1 and banner=1 order by a.id desc limit ?";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, limit);

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		List<Article> articleList = new ArrayList<>();
		while (rs.next()) {
//			id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));
			articleList.add(article);
		}

//		5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return articleList;
	}

	/**
	 * 通过分类ID获取文章列表
	 * 
	 * @param categoryId
	 * @return
	 * @throws SQLException
	 */
	public Page<Article> findByCategory(int categoryId, int pageNo) throws SQLException {

		String countSql = "select count(1) from article a inner join category b on a.category_id = b.id where a.category_id="
				+ categoryId + " and a.status = 1";
		int totalCount = this.countAll(countSql);
		Page<Article> page = new Page<>(pageNo);
		page.setTotalCount(totalCount);

		// 1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.category_id=? and a.status =1 order by a.create_time limit ?,? ";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, categoryId);
		st.setInt(2, page.getStart());
		st.setInt(3, page.getPageSize());
		

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		List<Article> articleList = new ArrayList<>();
		while (rs.next()) {
//			id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));
			articleList.add(article);
		}
		page.setData(articleList);

//		5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return page;
	}

	/**
	 * 通过分类ID获取文章列表
	 * 
	 * @param categoryId
	 * @return
	 * @throws SQLException
	 */
	public List<Article> findByCategory(int categoryId) throws SQLException {
		// 1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.category_id=? and a.status =1";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, categoryId);

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		List<Article> articleList = new ArrayList<>();
		while (rs.next()) {
//			id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));
			articleList.add(article);
		}

//		5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return articleList;
	}

	public int modifyView(int id) throws SQLException {
		// 1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "update article set view = view+1 where id=?";
//				3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, id);
		int result = st.executeUpdate();

//				5.关闭资源
		DBUtils.closeResource(conn, st);

		return result;
	}

	public Article findById(int id) throws SQLException {
		// 1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "select a.*,b.title as category_title from article a inner join category b on a.category_id=b.id where a.id=? and a.status=1";
//				3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, id);

		ResultSet rs = st.executeQuery();

		// 4.处理结果
		Article article = null;

		while (rs.next()) {
//					id,title,author,category_id,category_title,description,cover_img,content,view,create_time,update_time,status,banner
			article = new Article();
			article.setId(rs.getInt("id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setCategoryTitle(rs.getString("category_title"));
			article.setDescription(rs.getString("description"));
			article.setCoverImg(rs.getString("cover_img"));
			article.setContent(rs.getString("content"));
			article.setView(rs.getInt("view"));
			article.setCreateTime(rs.getDate("create_time"));
			article.setUpdateTime(rs.getDate("update_time"));
			article.setStatus(rs.getInt("status"));
			article.setBanner(rs.getBoolean("banner"));

		}

//				5.关闭资源
		DBUtils.closeResource(conn, st, rs);

		return article;
	}

	public int add(Article article) throws SQLException {
		// insert into article(title, author,category_id, description, cover_img,
		// content,create_time, update_time) values (?,?,?,?,?,?,?,?)
		// 1.获取链接
		Connection conn = DBUtils.getConnection();

		// 2.拼接SQL
		String sql = "insert into article(title, author,category_id, description, cover_img, content,create_time, update_time,view,status) values (?,?,?,?,?,?,?,?,?,?)";
//		3.获取执行者，发送SQL
		PreparedStatement st = conn.prepareStatement(sql);
		st.setString(1, article.getTitle());
		st.setString(2, article.getAuthor());
		st.setInt(3, article.getCategoryId());
		st.setString(4, article.getDescription());
		st.setString(5, article.getCoverImg());
		st.setString(6, article.getContent());
		st.setDate(7, new java.sql.Date(new Date().getTime()));
		st.setDate(8, new java.sql.Date(new Date().getTime()));
		st.setInt(9, 0);
		st.setInt(10, 1);

		int result = st.executeUpdate();

//		5.关闭资源
		DBUtils.closeResource(conn, st);

		return result;
	}

}
